/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-20
 * @Version: 071120.001
 * Data access object of tour item object
 * 
 * TODO: other dao functions, such as insert, update, delete functions
 */
package com.ibsol.database
{
	import com.ibsol.model.TourItem;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class TourItemDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * TourItemDAO default constructor, gets tourix database connection
		 * @param: null
		 */
		public function TourItemDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/** 
		 * Load specific TourItem according to package id
		 * @param:	packageid:int			package id
		 * 			resultHandler:Function	result function adapter, return tour item array collection
		 * 		  	faultHandler:Function	fault function adapter
		 * @return:	void
		 */
		public function loadTourItemsByPackage(packageid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM tourItem WHERE packageid=" + packageid + " AND offlineOperator !='DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
			 		resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load specific TourItem according to itinerary id
		 * @param: 	itineraryId:int			itinerary id, which tour item belongs to
		 * 		  	resultHandler:Function	result function adapter, return tour item array collection
		 * 		  	faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function loadTourItemsByItinerary(itineraryId:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT t.*, c.name as cityName FROM tourItem as t, city as c WHERE t.itineraryid=" +
				itineraryId + " AND t.offlineOperator !='DEL' AND t.cityid = c.id ORDER BY t.sequence ASC";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load tourItem (attraction) according to it's own id
		 * @param:	id:int					attraction id
		 * 			resultHandler:Function	result function adapter, return tourItem object
		 * 			faultHandler:Function	fault function adapter
		 * @return:	void
		 */
		public function loadTourItem(id:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT t.*, c.name as cityName, c.country FROM tourItem as t, city as c WHERE t.id=" + id +
					" AND t.cityid=c.id AND t.offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					try{
						resultHandler.call(this, typeObject(sqlStr.getResult().data[0]));
					}catch(e:Error){return;}
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Creates a new tourItem into database. For different level of user, there are different options
		 * tourItem (id, packageid, name, cityid, itineraryid, desp, offlineOperator)
		 * salesman level: 			insert only, no need to set offlineOperator. Cannot update database with server.
		 * administrator level:
		 * manager level:			after insertion of touritem, set offlineOperator to NEW. If there is a server connected,
		 * 							update data with server. Then reset offlineOperator.
		 * @param:	touritem:TourItem			touritem object will be inserted
		 * 			resultHandler:Function		result handler adapter, default is null
		 * 			faultHandler:Function		fault handler adapter, default is null
		 */
		public function insertTourItem(touritem:TourItem, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO tourItem (packageid, name, cityid, itineraryid, desp, offlineOperator, sequence, updateTime) " + 
					" VALUES (" + touritem.packageid + ",'" + touritem.name + "'," + touritem.cityid + "," + touritem.itineraryid +
					",'" + touritem.desp + "','" + touritem.offlineOperator + "'," + touritem.sequence + ", 'T')";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					touritem.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a touritem record in database. There are different options for different levels of users.
		 * tourItem (id, packageid, name, cityid, itineraryid, desp, offlineOperator, updateTime)
		 * salesman  level:		update only, no need to set offlineOperator. Cannot update database with server.
		 * administrator level:
		 * manager level:		after update of touritem record, set offlineOperator to EDIT. If there is a server connected, update
		 * 						data with server. Then reset offlineOperator.
		 * @param:	touritem:TourItem			touritem witch will be updated
		 * 			resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function updateTourItem(touritem:TourItem, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE tourItem SET packageid=" + touritem.packageid + ",name='" + touritem.name + "',cityid=" + touritem.cityid + 
				",itineraryid=" + touritem.itineraryid + ",desp='" + touritem.desp + "',offlineOperator='" + touritem.offlineOperator + 
				"',updateTime='T',sequence=" + touritem.sequence +" WHERE id=" + touritem.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes touritems according to its itinerary id offline, just updates the offline operator to "DEL"
		 * @param:		id:int						itinerary id, whose touritem will be deleted
		 * 				resultHandler:Function		result handler function adapter
		 * 				faultHandler:Function		fault handler function adapter
		 * @return:		void
		 */
		public function deleteTourItemsByItinerary(id:int, resultHandler:Function=null, faultHandler:Function=null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE tourItem SET offlineOperator='DEL' WHERE itineraryid=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes touritem by its own id locally, just updates the offline operator to "DEL"
		 * @param:		id:int						tourItem id, the deleted tourItem id
		 * 				resultHandler:Function		result handler function adapter
		 * 				faultHandler:Function		fault handler function adapter
		 * @return:		void
		 */
		public function deleteTourItem(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE tourItem SET offlineOperator='DEL' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permentately delete touritems from database, whose offlineOperator is set to "DEL". If user wants to delete photo,
		 * user just updates offlineOperator to "DEL". This function delete photos permentately from database.
		 * @param:	resultHandler:Function			result handler adapter
		 * 			faultHandler:Function			fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedTourItems(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE * FROM tourItem WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates tourItem sequence, and sets the offlineOperator to "EDIT"
		 * @param:	id:int					which tourItem to udpate
		 * 			sequence:int			sequence nunmber
		 * 			resultHandler:Function	result handler adapter
		 * 			faultHandler:Function	fault handler adatper
		 * @return:	void
		 */
		public function updateTourItemSequence(id:int, sequence:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE tourItem SET sequence=" + sequence + ",offlineOperator='EDIT', updateTime = 'T' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
			
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
		/**
		 * Converts database object to TourItem class
		 * @param: o:Object database return object
		 * @return: TourItem object, after conversion
		 */
		private function typeObject(o:Object):TourItem
		{
			var t:TourItem = new TourItem();
			t.id = o.id;
			t.packageid = o.packageid;
			t.name = o.name;
			t.cityid = o.cityid;
			t.cityName = o.cityName;
			t.country = o.country;
			t.itineraryid = o.itineraryid;
			t.desp = o.desp;
			t.offlineOperator = o.offlineOperator;
			t.updateTime = o.updateTime;
			t.sequence = o.sequence;
			return t;
		}
		/**
		 * Converts array object to TourItem array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection ArrayCollection of TourItem after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
	}
}